﻿Imports System.Data
Imports MyGeneration.dOOdads
Imports ClassSigeco.ClassSigeco

Public Class controller_stock
    'prueba de svn
    Private _MessageError As Exception = Nothing
    Public Property MessageError() As Exception
        Get
            Return _MessageError
        End Get
        Set(value As Exception)
            _MessageError = value
        End Set
    End Property

#Region "Mercaderias"

    Public Function Insert_mercaderia(ByVal conn As String, ByVal mercaderia As String, ByVal stockMinimo As Integer, ByVal idMarca As Integer, ByVal idRubro As Integer, ByVal observaciones As String, ByVal idImpuesto As Integer, ByVal actualizadoPor As Integer, ByVal codigoBarra As String) As Integer

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_mercaderia(conn)

            merca.AddNew()
            merca.Mercaderia = mercaderia
            merca.StockMinimo = stockMinimo
            merca.IdMarca = idMarca
            merca.IdRubro = idRubro
            merca.Observaciones = observaciones
            merca.IdImpuesto = idImpuesto
            merca.ActualizadoPor = actualizadoPor
            merca.ActualizadoFecha = DateTime.Now
            merca.CodigoBarra = codigoBarra
            merca.Save()

            tx.CommitTransaction()

            Return merca.IdMercaderia

        Catch ex As Exception
            tx.RollbackTransaction()
            Return 0
        End Try

    End Function

    Public Function Update_mercaderia(ByVal conn As String, ByVal idMercaderia As Integer, _
                                      ByVal mercaderia As String, ByVal stockMinimo As Integer, ByVal idMarca As Integer, ByVal idRubro As Integer, _
                                       ByVal observaciones As String, ByVal idImpuesto As Integer, ByVal actualizadoPor As Integer, ByVal codigoBarra As String) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_mercaderia(conn)
            merca.Where.IdMercaderia.Value = idMercaderia

            If merca.Query.Load Then

                merca.Mercaderia = mercaderia
                merca.StockMinimo = stockMinimo
                merca.IdMarca = idMarca
                merca.IdRubro = idRubro
                merca.Observaciones = observaciones
                merca.IdImpuesto = idImpuesto
                merca.ActualizadoPor = actualizadoPor
                merca.ActualizadoFecha = DateTime.Now
                merca.CodigoBarra = codigoBarra
                merca.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()

            Return False

        End Try

    End Function

    Public Function Update_mercaderia_lote(ByVal conn As String, ByVal idMercaderiaLote As Integer, _
                                       ByVal saldo As Decimal, ByVal actualizadoPor As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_mercaderiaLote(conn)
            merca.Where.IdMercaderiaLote.Value = idMercaderiaLote

            If merca.Query.Load Then

                merca.Saldo = saldo
                merca.IdUsuarioBaja = actualizadoPor
                merca.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()

            Return False

        End Try

    End Function

    Public Function Delete_mercaderia(ByVal conn As String, ByVal idMercaderia As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_mercaderia(conn)
            merca.Where.IdMercaderia.Value = idMercaderia

            If merca.Query.Load Then

                merca.MarkAsDeleted()
                merca.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    Public Function Baja_mercaderia(ByVal conn As String, ByVal idMercaderiaLote As Integer, ByVal idUsuario As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_mercaderiaLote(conn)
            merca.Where.IdMercaderiaLote.Value = idMercaderiaLote

            If merca.Query.Load Then
                merca.Saldo = 0
                merca.FechaBaja = DateTime.Now
                merca.IdUsuarioBaja = idUsuario
                merca.Save()
            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    Public Function get_mercaderias_All(ByVal conn As String, ByVal idMercaderia As Integer, ByVal mercaderia As String, _
                                        ByVal idMarca As Integer, ByVal idRubro As Integer, _
                                        ByVal esVenta As Boolean, ByVal codigoBarra As String, _
                                        ByVal idDeposito As Integer, ByVal idMercaderiaLote As Integer) As DataTable
        Try

            Dim vmercas As New vw_mercaderias(conn)
            Dim vmercasLotes As New vw_mercaderias_lotes(conn)

            If idMercaderia > 0 Then
                vmercas.Where.IdMercaderia.Value = idMercaderia
                vmercasLotes.Where.IdMercaderia.Value = idMercaderia
            End If

            If idMercaderiaLote > 0 Then
                vmercas.Where.IdMercaderiaLote.Value = idMercaderiaLote
                vmercasLotes.Where.IdMercaderiaLote.Value = idMercaderiaLote
            End If

            If codigoBarra <> "" Then
                vmercas.Where.CodigoBarra.Value = codigoBarra
                vmercasLotes.Where.CodigoBarra.Value = codigoBarra
            End If

            If idDeposito > 0 Then
                vmercasLotes.Where.IdDeposito.Value = idDeposito
            End If

            If mercaderia <> "" Then
                vmercas.Where.Mercaderia.Value = "%" & mercaderia & "%"
                vmercas.Where.Mercaderia.Operator = WhereParameter.Operand.Like

                vmercasLotes.Where.Mercaderia.Value = "%" & mercaderia & "%"
                vmercasLotes.Where.Mercaderia.Operator = WhereParameter.Operand.Like
            End If

            If idMarca > 0 Then
                vmercas.Where.IdMarca.Value = idMarca
                vmercasLotes.Where.IdMarca.Value = idMarca
            End If

            If idRubro > 0 Then
                vmercas.Where.IdRubro.Value = idRubro
                vmercasLotes.Where.IdRubro.Value = idRubro
            End If

            If esVenta Then
                vmercasLotes.Where.Saldo.Value = 0
                vmercasLotes.Where.Saldo.Operator = WhereParameter.Operand.GreaterThan

                vmercasLotes.Where.FechaBaja.Value = 0
                vmercasLotes.Where.FechaBaja.Operator = WhereParameter.Operand.IsNull
            End If

            vmercas.Query.AddOrderBy("mercaderia", WhereParameter.Dir.ASC)
            vmercasLotes.Query.AddOrderBy("mercaderia", WhereParameter.Dir.ASC)

            If esVenta Then

                If vmercasLotes.Query.Load Then
                    If vmercasLotes.DefaultView.Table.Rows.Count > 0 Then
                        Return vmercasLotes.DefaultView.Table
                    Else
                        Return Nothing
                    End If

                Else
                    Return Nothing
                End If

            Else

                If vmercas.Query.Load Then
                    If vmercas.DefaultView.Table.Rows.Count > 0 Then
                        Return vmercas.DefaultView.Table
                    Else
                        Return Nothing
                    End If
                Else
                    Return Nothing
                End If

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_mercaderias_lotes_All(ByVal conn As String, ByVal idMercaderia As Integer, ByVal mercaderia As String, _
                                        ByVal idMarca As Integer, ByVal idRubro As Integer, _
                                        ByVal esVenta As Boolean, ByVal codigoBarra As String, _
                                        ByVal idDeposito As Integer, ByVal idMercaderiaLote As Integer) As DataTable
        Try

            'Dim vmercas As New vw_mercaderias(conn)
            Dim vmercasLotes As New vw_mercaderias_lotes(conn)

            If idMercaderia > 0 Then
                'vmercas.Where.IdMercaderia.Value = idMercaderia
                vmercasLotes.Where.IdMercaderia.Value = idMercaderia
            End If

            If idMercaderiaLote > 0 Then
                'vmercas.Where.IdMercaderiaLote.Value = idMercaderiaLote
                vmercasLotes.Where.IdMercaderiaLote.Value = idMercaderiaLote
            End If

            If codigoBarra <> "" Then
                'vmercas.Where.CodigoBarra.Value = codigoBarra
                vmercasLotes.Where.CodigoBarra.Value = codigoBarra
            End If

            If idDeposito > 0 Then
                vmercasLotes.Where.IdDeposito.Value = idDeposito
            End If

            If mercaderia <> "" Then
                'vmercas.Where.Mercaderia.Value = "%" & mercaderia & "%"
                'vmercas.Where.Mercaderia.Operator = WhereParameter.Operand.Like

                vmercasLotes.Where.Mercaderia.Value = "%" & mercaderia & "%"
                vmercasLotes.Where.Mercaderia.Operator = WhereParameter.Operand.Like
            End If

            If idMarca > 0 Then
                'vmercas.Where.IdMarca.Value = idMarca
                vmercasLotes.Where.IdMarca.Value = idMarca
            End If

            If idRubro > 0 Then
                'vmercas.Where.IdRubro.Value = idRubro
                vmercasLotes.Where.IdRubro.Value = idRubro
            End If

            If esVenta Then
                vmercasLotes.Where.Saldo.Value = 0
                vmercasLotes.Where.Saldo.Operator = WhereParameter.Operand.GreaterThan

                vmercasLotes.Where.FechaBaja.Value = 0
                vmercasLotes.Where.FechaBaja.Operator = WhereParameter.Operand.IsNull
            End If

            'vmercas.Query.AddOrderBy("mercaderia", WhereParameter.Dir.ASC)
            vmercasLotes.Query.AddOrderBy("mercaderia", WhereParameter.Dir.ASC)

            If esVenta Then

                If vmercasLotes.Query.Load Then
                    If vmercasLotes.DefaultView.Table.Rows.Count > 0 Then
                        Return vmercasLotes.DefaultView.Table
                    Else
                        Return Nothing
                    End If

                Else
                    Return Nothing
                End If

            Else

                If vmercasLotes.Query.Load Then
                    If vmercasLotes.DefaultView.Table.Rows.Count > 0 Then
                        Return vmercasLotes.DefaultView.Table
                    Else
                        Return Nothing
                    End If
                Else
                    Return Nothing
                End If

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_motivos_stock_devolucion(ByVal conn As String, ByVal esDevolucion As Boolean) As DataTable
        Try

            Dim obj As New si_motivoStock(conn)

            obj.Where.Devolucion.Value = esDevolucion

            'If Not esDevolucion Then
            '    obj.Where.IdMotivoStock.Value = idMotivoVenta
            '    obj.Where.IdMotivoStock.Operator = WhereParameter.Operand.NotEqual
            'End If

            obj.Query.AddOrderBy("Motivo", WhereParameter.Dir.ASC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_motivos_stock_all(ByVal conn As String, ByVal soloCompras As Boolean) As DataTable
        Try

            Dim obj As New si_motivoStock(conn)

            If (soloCompras) Then
                obj.Where.Motivo.Value = "VENTA DE MERCADERIA"
                obj.Where.Motivo.Operator = WhereParameter.Operand.NotEqual

                obj.Where.Devolucion.Value = False
            End If

            obj.Query.AddOrderBy("Motivo", WhereParameter.Dir.ASC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_stock_minimo(ByVal conn As String) As DataTable
        Try

            Dim obj As New vw_mercaderia_stock_minimo(conn)
            obj.Where.Minimo.Value = 0
            obj.Where.Minimo.Operator = WhereParameter.Operand.LessThanOrEqual

            obj.Query.AddOrderBy("mercaderia", WhereParameter.Dir.ASC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_costo_mercaderia(ByVal conn As String) As DataTable
        Try

            Dim obj As New vw_mercaderias_costos(conn)

            obj.Query.AddOrderBy("mercaderia", WhereParameter.Dir.ASC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_mercaderia_ranking(ByVal conn As String) As DataTable
        Try

            Dim obj As New vw_ventas_mercaderia_ranking(conn)
            obj.Where.Total_facturado.Value = 0
            obj.Where.Total_facturado.Operator = WhereParameter.Operand.GreaterThan

            obj.Query.AddOrderBy("total_facturado", WhereParameter.Dir.DESC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_marcas(ByVal conn As String, ByVal idMarca As Integer, ByVal marca As String) As DataTable
        Try

            Dim obj As New si_marca(conn)

            If idMarca > 0 Then
                obj.Where.IdMarca.Value = idMarca
            End If

            If marca <> "" Then
                obj.Where.Marca.Value = "%" & marca & "%"
                obj.Where.Marca.Operator = WhereParameter.Operand.Like
            End If

            obj.Query.AddOrderBy("Marca", WhereParameter.Dir.ASC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_rubros(ByVal conn As String, ByVal idRubro As Integer, ByVal rubro As String) As DataTable
        Try

            Dim obj As New si_rubro(conn)

            If idRubro > 0 Then
                obj.Where.IdRubro.Value = idRubro
            End If

            If rubro <> "" Then
                obj.Where.Rubro.Value = "%" & rubro & "%"
                obj.Where.Rubro.Operator = WhereParameter.Operand.Like
            End If

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_impuestos(ByVal conn As String, ByVal idImpuesto As Integer, ByVal impuesto As String) As DataTable
        Try

            Dim obj As New si_impuesto(conn)

            If idImpuesto > 0 Then
                obj.Where.IdImpuesto.Value = idImpuesto
            End If

            If impuesto <> "" Then
                obj.Where.Impuesto.Value = "%" & impuesto & "%"
                obj.Where.Impuesto.Operator = WhereParameter.Operand.Like
            End If

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function


    Public Function Insert_marcas(ByVal conn As String, ByVal marcaDescrip As String) As Integer

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim marca As New si_marca(conn)

            marca.AddNew()
            marca.Marca = marcaDescrip
            marca.Save()

            tx.CommitTransaction()

            Return marca.IdMarca

        Catch ex As Exception
            tx.RollbackTransaction()
            If ex.Message.Contains("ix_marca_unica") Then
                MessageError = New Exception("Ya existe una marca con la misma descripcion. Favor verifique.")
            Else
                MessageError = ex
            End If
            Return 0
        End Try

    End Function

    Public Function Update_marca(ByVal conn As String, ByVal idMarca As Integer, ByVal marcaDescrip As String) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_marca(conn)
            merca.Where.IdMarca.Value = idMarca

            If merca.Query.Load Then

                merca.Marca = marcaDescrip
                merca.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()

            If ex.Message.Contains("ix_marca_unica") Then
                MessageError = New Exception("Ya existe una marca con la misma descripcion. Favor verifique.")
            Else
                MessageError = ex
            End If

            Return False

        End Try

    End Function

    Public Function Delete_marcas(ByVal conn As String, ByVal idMarca As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim marca As New si_marca(conn)
            marca.Where.IdMarca.Value = idMarca

            If marca.Query.Load Then

                marca.MarkAsDeleted()
                marca.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function


    Public Function Insert_rubro(ByVal conn As String, ByVal rubroDescrip As String) As Integer

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim rubro As New si_rubro(conn)

            rubro.AddNew()
            rubro.Rubro = rubroDescrip
            rubro.Save()

            tx.CommitTransaction()

            Return rubro.IdRubro

        Catch ex As Exception
            tx.RollbackTransaction()

            If ex.Message.Contains("ix_rubro_unico") Then
                MessageError = New Exception("El rubro ingresado ya existem, favor verifiquelo.")
            Else
                MessageError = ex
            End If

            Return 0
        End Try

    End Function

    Public Function Update_rubro(ByVal conn As String, ByVal idRubro As Integer, ByVal rubroDescrip As String) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_rubro(conn)
            merca.Where.IdRubro.Value = idRubro

            If merca.Query.Load Then

                merca.Rubro = rubroDescrip
                merca.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()

            If ex.Message.Contains("ix_rubro_unico") Then
                MessageError = New Exception("El rubro ingresado ya existem, favor verifique.")
            Else
                MessageError = ex
            End If

            Return False

        End Try

    End Function

    Public Function Delete_rubro(ByVal conn As String, ByVal idRubro As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim rubro As New si_rubro(conn)
            rubro.Where.IdRubro.Value = idRubro

            If rubro.Query.Load Then

                rubro.MarkAsDeleted()
                rubro.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function


    Public Function Insert_impuesto(ByVal conn As String, ByVal impuestoDescrip As String, ByVal valor As Decimal) As Integer

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim impuesto As New si_impuesto(conn)

            impuesto.AddNew()
            impuesto.Impuesto = impuestoDescrip
            impuesto.Valor = valor
            impuesto.Save()

            tx.CommitTransaction()

            Return impuesto.IdImpuesto

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return 0
        End Try

    End Function

    Public Function Update_impuesto(ByVal conn As String, ByVal idImpuesto As Integer, ByVal impuestoDescrip As String, ByVal valor As Decimal) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_impuesto(conn)
            merca.Where.IdImpuesto.Value = idImpuesto

            If merca.Query.Load Then

                merca.Impuesto = impuestoDescrip
                merca.Valor = valor
                merca.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    Public Function Delete_impuesto(ByVal conn As String, ByVal idImpuesto As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim impuesto As New si_impuesto(conn)
            impuesto.Where.IdImpuesto.Value = idImpuesto

            If impuesto.Query.Load Then

                impuesto.MarkAsDeleted()
                impuesto.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    Public Function get_Movimiento_EstadoStock(ByVal conn As String, ByVal idMercaderia As Integer, _
                                              ByVal fechaInicio As DateTime, ByVal fechaFin As DateTime) As DataTable
        Try

            Dim vPro As New ClassSigeco.ClassSigeco.si_usuario(conn)
            Dim ds As New DataTable

            ds = get_MovimientoStock_All(conn, fechaInicio, fechaFin, idMercaderia)

            If Not ds Is Nothing Then

                Dim saldoAnt As Decimal = 0

                If ds.Rows.Count > 0 Then

                    For i As Integer = 0 To ds.Rows.Count - 1
                        If i > 0 Then
                            ds.Rows(i)("Saldo") = ds.Rows(i - 1)("Saldo") + ds.Rows(i)("Saldo")
                        End If
                    Next

                    saldoAnt = ds.Rows(ds.Rows.Count - 1)("Saldo")

                End If

                'If ds.Tables(1).Rows.Count > 0 Then

                '    For i As Integer = 0 To ds.Tables(1).Rows.Count - 1

                '        If i > 0 Then
                '            ds.Tables(1).Rows(i)("totalSaldo") = ds.Tables(1).Rows(i - 1)("totalSaldo") + ds.Tables(1).Rows(i)("totalSaldo")
                '        Else
                '            ds.Tables(1).Rows(i)("totalSaldo") = ds.Tables(1).Rows(i)("totalSaldo") + saldoAnt
                '        End If

                '    Next

                'End If

                'If ds.Tables(2).Rows.Count > 0 Then

                '    For i As Integer = 0 To ds.Tables(2).Rows.Count - 1
                '        If i > 0 Then
                '            ds.Tables(2).Rows(i)("totalSaldo") = ds.Tables(2).Rows(i - 1)("totalSaldo") + ds.Tables(2).Rows(i)("totalSaldo")
                '        Else
                '            ds.Tables(2).Rows(i)("totalSaldo") = ds.Tables(2).Rows(i)("totalSaldo") + saldoAnt
                '        End If
                '    Next

                'End If

                'If ds.Rows.Count = 3 Then
                'Else

                '    'If ds.Tables(0).Rows.Count > 0 Then

                '    '    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                '    '        If i > 0 Then
                '    '            ds.Tables(0).Rows(i)("totalSaldo") = ds.Tables(0).Rows(i - 1)("totalSaldo") + ds.Tables(0).Rows(i)("totalSaldo")
                '    '        End If
                '    '    Next

                '    'End If

                '    'If ds.Tables(1).Rows.Count > 0 Then

                '    '    For i As Integer = 0 To ds.Tables(1).Rows.Count - 1
                '    '        If i > 0 Then
                '    '            ds.Tables(1).Rows(i)("totalSaldo") = ds.Tables(1).Rows(i - 1)("totalSaldo") + ds.Tables(1).Rows(i)("totalSaldo")
                '    '        End If
                '    '    Next

                '    'End If

                'End If

                Return ds

            Else
                Return Nothing
            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_MovimientoStock_All(ByVal conn As String, ByVal fechaInicio As DateTime, _
                                   ByVal fechaFin As DateTime, ByVal idMercaderia As Integer) As DataTable
        Try

            Dim vNotas As New ClassSigeco.ClassSigeco.vw_movimiento_stock(conn)

            If idMercaderia > 0 Then
                vNotas.Where.IdMercaderia.Value = idMercaderia
            End If


            vNotas.Where.Fecha.BetweenBeginValue = New DateTime(fechaInicio.Year, fechaInicio.Month, fechaInicio.Day, 0, 0, 0)
            vNotas.Where.Fecha.BetweenEndValue = New DateTime(fechaFin.Year, fechaFin.Month, fechaFin.Day, 23, 59, 59)
            vNotas.Where.Fecha.Operator = WhereParameter.Operand.Between

            vNotas.Query.AddOrderBy("fecha", WhereParameter.Dir.ASC)

            If vNotas.Query.Load Then
                If vNotas.DefaultView.Table.Rows.Count > 0 Then
                    Return vNotas.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_Movimiento_Stock_EstadoCuenta(ByVal conn As String, ByVal idMercaderia As Integer, _
                                              ByVal fechaInicio As DateTime, ByVal fechaFin As DateTime) As DataSet
        Try

            Dim vPro As New ClassSigeco.ClassSigeco.si_usuario(conn)
            Dim ds As New DataSet

            ds = vPro.get_estado_movimiento_stock(conn, idMercaderia, fechaInicio, fechaFin)

            If Not ds Is Nothing Then

                If ds.Tables.Count = 3 Then

                    Dim saldoAnt As Decimal = 0

                    If ds.Tables(0).Rows.Count > 0 Then

                        For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                            If i > 0 Then
                                ds.Tables(0).Rows(i)("totalSaldo") = ds.Tables(0).Rows(i - 1)("totalSaldo") + ds.Tables(0).Rows(i)("totalSaldo")
                            End If
                        Next

                        saldoAnt = ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1)("totalSaldo")

                    End If

                    If ds.Tables(1).Rows.Count > 0 Then

                        For i As Integer = 0 To ds.Tables(1).Rows.Count - 1

                            If i > 0 Then
                                ds.Tables(1).Rows(i)("totalSaldo") = ds.Tables(1).Rows(i - 1)("totalSaldo") + ds.Tables(1).Rows(i)("totalSaldo")
                            Else
                                ds.Tables(1).Rows(i)("totalSaldo") = ds.Tables(1).Rows(i)("totalSaldo") + saldoAnt
                            End If

                        Next

                    End If

                    If ds.Tables(2).Rows.Count > 0 Then

                        For i As Integer = 0 To ds.Tables(2).Rows.Count - 1
                            If i > 0 Then
                                ds.Tables(2).Rows(i)("totalSaldo") = ds.Tables(2).Rows(i - 1)("totalSaldo") + ds.Tables(2).Rows(i)("totalSaldo")
                            Else
                                ds.Tables(2).Rows(i)("totalSaldo") = ds.Tables(2).Rows(i)("totalSaldo") + saldoAnt
                            End If
                        Next

                    End If

                Else

                    If ds.Tables(0).Rows.Count > 0 Then

                        For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                            If i > 0 Then
                                ds.Tables(0).Rows(i)("totalSaldo") = ds.Tables(0).Rows(i - 1)("totalSaldo") + ds.Tables(0).Rows(i)("totalSaldo")
                            End If
                        Next

                    End If

                    If ds.Tables(1).Rows.Count > 0 Then

                        For i As Integer = 0 To ds.Tables(1).Rows.Count - 1
                            If i > 0 Then
                                ds.Tables(1).Rows(i)("totalSaldo") = ds.Tables(1).Rows(i - 1)("totalSaldo") + ds.Tables(1).Rows(i)("totalSaldo")
                            End If
                        Next

                    End If

                End If

                Return ds

            Else
                Return Nothing
            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    '------------------------------

    Public Function Insert_deposito(ByVal conn As String, ByVal descripcion As String, ByVal ubicacion As String, ByVal idUsuario As Integer) As Integer

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try

            tx.BeginTransaction()

            Dim deposito As New si_deposito(conn)

            deposito.AddNew()
            deposito.Descripcion = descripcion
            deposito.Ubicacion = ubicacion
            deposito.Habilitado = True
            deposito.ActualizadoPor = idUsuario
            deposito.ActualizadoFecha = DateTime.Now
            deposito.Save()

            tx.CommitTransaction()

            Return deposito.IdDeposito

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return 0
        End Try

    End Function

    Public Function Update_deposito(ByVal conn As String, ByVal idDeposito As Integer, _
                                    ByVal descripcion As String, ByVal ubicacion As String, ByVal idUsuario As Integer, ByVal habilitado As Boolean) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim deposito As New si_deposito(conn)
            deposito.Where.IdDeposito.Value = idDeposito

            If deposito.Query.Load Then
                deposito.Descripcion = descripcion
                deposito.Ubicacion = ubicacion
                deposito.ActualizadoPor = idUsuario
                deposito.Habilitado = habilitado
                deposito.ActualizadoFecha = DateTime.Now
                deposito.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    Public Function Delete_deposito(ByVal conn As String, ByVal idDeposito As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim deposito As New si_deposito(conn)
            deposito.Where.IdDeposito.Value = idDeposito

            If deposito.Query.Load Then

                deposito.MarkAsDeleted()
                deposito.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    Public Function get_depositos(ByVal conn As String, ByVal idDeposito As Integer, _
                                  ByVal descripcion As String, ByVal ubicacion As String, ByVal habilitado As Boolean) As DataTable
        Try

            Dim deposito As New vw_depositos(conn)

            If idDeposito > 0 Then
                deposito.Where.IdDeposito.Value = idDeposito
            ElseIf habilitado = True Then
                deposito.Where.Habilitado.Value = habilitado
            End If

            If descripcion <> "" Then
                deposito.Where.Descripcion.Value = "%" & descripcion & "%"
                deposito.Where.Descripcion.Operator = WhereParameter.Operand.Like
            End If

            If ubicacion <> "" Then
                deposito.Where.Ubicacion.Value = "%" & ubicacion & "%"
                deposito.Where.Ubicacion.Operator = WhereParameter.Operand.Like
            End If

            deposito.Query.AddOrderBy("DESCRIPCION", WhereParameter.Dir.ASC)

            If deposito.Query.Load Then
                Return deposito.DefaultView.Table
            Else
                Return Nothing
            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_depositos_NonDestino(ByVal conn As String, ByVal idDepositoDestino As Integer) As DataTable
        Try

            Dim deposito As New vw_depositos(conn)

            If idDepositoDestino > 0 Then
                deposito.Where.IdDeposito.Value = idDepositoDestino
                deposito.Where.IdDeposito.Operator = WhereParameter.Operand.NotEqual
                deposito.Where.Habilitado.Value = True
            End If

            deposito.Query.AddOrderBy("DESCRIPCION", WhereParameter.Dir.ASC)

            If deposito.Query.Load Then
                Return deposito.DefaultView.Table
            Else
                Return Nothing
            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

#End Region

#Region "Lista de precio"

    Public Function Insert_lista_precio(ByVal conn As String, ByVal fechaInicio As DateTime, ByVal fechaFin As DateTime, ByVal idAdministrador As Integer, ByVal descripcion As String, ByVal actualizadoPor As Integer) As Integer

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_mercaderiaListaPrecio(conn)

            merca.AddNew()
            merca.FechaInicio = New DateTime(fechaInicio.Year, fechaInicio.Month, fechaInicio.Day, 0, 0, 0)
            merca.FechaFin = New DateTime(fechaFin.Year, fechaFin.Month, fechaFin.Day, 23, 59, 59)
            merca.IdAdministrador = idAdministrador
            merca.Descripcion = descripcion
            merca.ActualizadoPor = actualizadoPor
            merca.ActualizadoFecha = DateTime.Now

            merca.Save()

            tx.CommitTransaction()

            Return merca.IdListaPrecio

        Catch ex As Exception
            tx.RollbackTransaction()
            Return 0
        End Try

    End Function

    Public Function Update_lista_precio(ByVal conn As String, ByVal idListaPrecio As Integer, ByVal fechaInicio As DateTime, ByVal fechaFin As DateTime, ByVal idAdministrador As Integer, ByVal descripcion As String, ByVal actualizadoPor As Integer) As Integer

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_mercaderiaListaPrecio(conn)
            merca.Where.IdListaPrecio.Value = idListaPrecio
            If merca.Query.Load Then
                merca.FechaInicio = New DateTime(fechaInicio.Year, fechaInicio.Month, fechaInicio.Day, 0, 0, 0)
                merca.FechaFin = New DateTime(fechaFin.Year, fechaFin.Month, fechaFin.Day, 23, 59, 59)
                merca.IdAdministrador = idAdministrador
                merca.Descripcion = descripcion
                merca.ActualizadoPor = actualizadoPor
                merca.ActualizadoFecha = DateTime.Now

                merca.Save()
            End If


            tx.CommitTransaction()

            Return merca.IdListaPrecio

        Catch ex As Exception
            tx.RollbackTransaction()
            Return 0
        End Try

    End Function

    Public Function Delete_lista_precio(ByVal conn As String, ByVal idListaPrecio As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim impuesto As New si_mercaderiaListaPrecio(conn)
            impuesto.Where.IdListaPrecio.Value = idListaPrecio

            If impuesto.Query.Load Then

                impuesto.MarkAsDeleted()
                impuesto.Save()

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    '----------------------------------

    Public Function Insert_lista_precio_detalle(ByVal conn As String, ByVal idListaPrecio As Integer, ByVal idMercaderia As Integer, ByVal precio1 As Decimal, ByVal precio2 As Decimal, ByVal precio3 As Decimal) As Integer

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim merca As New si_mercaderiaListaPrecio_detalle(conn)

            merca.AddNew()
            merca.IdListaPrecio = idListaPrecio
            merca.IdMercaderia = idMercaderia
            merca.Precio1 = precio1
            merca.Precio2 = precio2
            merca.Precio3 = precio3
            merca.Save()

            tx.CommitTransaction()

            Return merca.IdListaPrecioDetalle

        Catch ex As Exception
            tx.RollbackTransaction()
            Return 0
        End Try

    End Function

    Public Function Delete_lista_precio_detalle(ByVal conn As String, ByVal idListaPrecioDetalle As Integer, ByVal idListaPrecio As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim impuesto As New si_mercaderiaListaPrecio_detalle(conn)

            If idListaPrecio > 0 Then
                impuesto.Where.IdListaPrecio.Value = idListaPrecio
            End If

            If idListaPrecioDetalle > 0 Then
                impuesto.Where.IdListaPrecioDetalle.Value = idListaPrecioDetalle
            End If

            If impuesto.Query.Load Then

                If impuesto.DefaultView.Table.Rows.Count = 1 Then
                    impuesto.MarkAsDeleted()
                    impuesto.Save()
                ElseIf impuesto.DefaultView.Table.Rows.Count > 1 Then

                    For i As Integer = 0 To impuesto.DefaultView.Table.Rows.Count - 1

                        Dim impuestoD As New si_mercaderiaListaPrecio_detalle(conn)
                        impuestoD.Where.IdListaPrecioDetalle.Value = impuesto.DefaultView.Table.Rows(i)("idListaPrecioDetalle")

                        If impuestoD.Query.Load Then
                            impuestoD.MarkAsDeleted()
                            impuestoD.Save()
                        End If

                    Next

                End If

            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    '----------------------------

    Public Function Update_Lista_Precio_Estado(ByVal conn As String, ByVal codListaPrecio As Integer, _
    ByVal codUsuario As Integer) As Boolean

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try
            tx.BeginTransaction()

            Dim compras As New ClassSigeco.ClassSigeco.si_mercaderiaListaPrecio(conn)
            compras.Where.IdListaPrecio.Value = codListaPrecio

            If compras.Query.Load Then
                compras.IdAdministrador = codUsuario
                compras.ActualizadoPor = codUsuario
                compras.ActualizadoFecha = DateTime.Now
                compras.Save()
            End If

            tx.CommitTransaction()
            Return True

        Catch ex As Exception
            tx.RollbackTransaction()
            MessageError = ex
            Return False
        End Try

    End Function

    Public Function get_listas_precios_by_id(ByVal conn As String, ByVal idListaPrecio As Integer) As DataTable
        Try

            Dim obj As New vw_lista_precio(conn)
            obj.Where.IdListaPrecio.Value = idListaPrecio
            obj.Query.AddOrderBy("fechaFin", WhereParameter.Dir.DESC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_listas_precios(ByVal conn As String, ByVal fechaInicio As DateTime, ByVal fechaFin As DateTime, ByVal descripcion As String, ByVal fechaTermino As Boolean) As DataTable
        Try

            Dim obj As New vw_lista_precio(conn)

            If descripcion <> "" Then
                obj.Where.Descripcion.Value = "%" & descripcion & "%"
                obj.Where.Descripcion.Operator = WhereParameter.Operand.Like
            End If

            If fechaTermino Then
                obj.Where.FechaFin.BetweenBeginValue = New DateTime(fechaInicio.Year, fechaInicio.Month, fechaInicio.Day, 0, 0, 0)
                obj.Where.FechaFin.BetweenEndValue = New DateTime(fechaFin.Year, fechaFin.Month, fechaFin.Day, 23, 59, 59)
                obj.Where.FechaFin.Operator = WhereParameter.Operand.Between
            Else
                obj.Where.FechaInicio.BetweenBeginValue = New DateTime(fechaInicio.Year, fechaInicio.Month, fechaInicio.Day, 0, 0, 0)
                obj.Where.FechaInicio.BetweenEndValue = New DateTime(fechaFin.Year, fechaFin.Month, fechaFin.Day, 23, 59, 59)
                obj.Where.FechaInicio.Operator = WhereParameter.Operand.Between
            End If

            obj.Query.AddOrderBy("fechaFin", WhereParameter.Dir.DESC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_listas_precios_detalles(ByVal conn As String, ByVal idListaPrecio As Integer, _
                                                ByVal mercaderia As String, ByVal idMercaderia As Integer, _
                                                ByVal marca As String, ByVal rubro As String, ByVal idMarca As Integer, _
                                                ByVal idRubro As Integer) As DataTable
        Try

            Dim obj As New vw_lista_precio_detalle(conn)

            If mercaderia <> "" Then
                obj.Where.Mercaderia.Value = "%" & mercaderia & "%"
                obj.Where.Mercaderia.Operator = WhereParameter.Operand.Like
            End If

            If idListaPrecio Then
                obj.Where.IdListaPrecio.Value = idListaPrecio
            End If

            If idMercaderia Then
                obj.Where.IdMercaderia.Value = idMercaderia
            End If

            obj.Query.AddOrderBy("mercaderia", WhereParameter.Dir.ASC)

            If obj.Query.Load Then
                If obj.DefaultView.Table.Rows.Count > 0 Then
                    Return obj.DefaultView.Table
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

    Public Function get_listas_precios_mercaderia(ByVal conn As String, ByVal idMercaderia As Integer, _
                                                ByVal fechaOperacion As DateTime) As DataTable
        Try

            Dim obj As New si_usuario(conn)
            Dim ds As New DataSet

            ds = obj.get_mercaderia_precio(conn, idMercaderia, fechaOperacion)

            If Not ds Is Nothing Then
                If ds.Tables(0).Rows.Count > 0 Then
                    Return ds.Tables(0)
                Else
                    Return Nothing
                End If

            Else
                Return Nothing

            End If

        Catch ex As Exception
            MessageError = ex
            Return Nothing
        End Try

    End Function

#End Region

End Class
